package jnpf.database.sql.enums;

import jnpf.database.constant.DbAliasConst;
import jnpf.database.datatype.db.interfaces.DtInterface;
import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.ParamEnum;
import jnpf.database.source.DbBase;
import jnpf.database.sql.enums.base.SqlFrameBase;
import jnpf.database.sql.model.DbStruct;
import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.List;

/**
 * MySQL SQL语句模板
 *
 * @author JNPF开发平台组 YanYu
 * @version V3.3
 * @copyright 引迈信息技术有限公司
 * @date 2022/3/17
 */
@Getter
@AllArgsConstructor
public enum SqlMySQLEnum implements SqlFrameBase {


    /* =============================== 系统语句 ==================================== */

    FIELDS("SELECT "
            + "NUMERIC_SCALE                        AS "    + DtInterface.NUMERIC_SCALE                  + ", "
            + "NUMERIC_PRECISION                    AS "    + DtInterface.NUMERIC_PRECISION              + ", "
            + "CHARACTER_MAXIMUM_LENGTH             AS "    + DtInterface.CHARACTER_LENGTH       + ", "
            + "COLUMN_NAME                          AS "    + DbAliasConst.FIELD                       + ", "
            + "DATA_TYPE                            AS "    + DbAliasConst.DATA_TYPE                        + ", "
            + "COLUMN_COMMENT                       AS "    + DbAliasConst.FIELD_COMMENT                    + ", "
            + "COLUMN_DEFAULT                       AS "    + DbAliasConst.DEFAULT_VALUE                    + ", "
            + "IF ( IS_NULLABLE = 'YES', '1', '0' ) AS "    + DbAliasEnum.ALLOW_NULL.AS()                       + ", "
            + "IF ( COLUMN_KEY = 'PRI', '1', '0' )  AS "    + DbAliasConst.PRIMARY_KEY                      + " "
            + "FROM "
            + "INFORMATION_SCHEMA.COLUMNS "
            + "WHERE "
            + "TABLE_NAME = " + ParamEnum.TABLE.getParamSign()
            + " AND "
            + "TABLE_SCHEMA= " + ParamEnum.DB_SCHEMA.getParamSign() + " ORDER  BY ORDINAL_POSITION;"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
            list.add(dbStruct.getMysqlDbName());
        }
    },
    TABLES("SELECT "
            + "table_name     AS " + DbAliasConst.TABLE_NAME        + ", "
            + "table_rows     AS " + DbAliasConst.TABLE_SUM         + ", "
            + "data_length    AS " + DbAliasConst.TABLE_SIZE        + ", "
            + "table_comment  AS " + DbAliasConst.TABLE_COMMENT     + " "
            + "FROM "
            + "INFORMATION_SCHEMA.TABLES "
            + "WHERE "
            + "TABLE_SCHEMA = "   + ParamEnum.DB_NAME.getParamSign()
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(dbStruct.getMysqlDbName());
        }
    },
    TABLE(
            TABLES.sqlFrame + "AND table_name = " + ParamEnum.TABLE.getParamSign()
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(dbStruct.getMysqlDbName());
            list.add(table);
        }
    },
    EXISTS_TABLE(
            "SELECT COUNT(*) AS TOTAL FROM (" +
            "SELECT "
            + "table_name AS "    + DbAliasConst.TABLE_NAME           + " "
            + "FROM "
            + "information_schema.TABLES "
            + "WHERE "
            + "TABLE_SCHEMA = "   + ParamEnum.DB_NAME.getParamSign()  + " "
            + "and "
            + "table_name = "     + ParamEnum.TABLE.getParamSign()
            + ") AS COUNT_TAB"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(dbStruct.getMysqlDbName());
            list.add(table);
        }
    },
    /* =============================== 定义语句 ==================================== */
    // （Data Definition Language）简称 DDL：用来建立数据库、数据库对象和定义列的命令。包括：create、alter、drop
    CREATE_TABLE    ("<CREATE> <TABLE> {table} <(>\n" +
            "【{column} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<PRIMARY KEY>] [AUTO_INCREMENT] [<COMMENT> {comment}]】" +
            "<)> "),

    CREATE(
            "<CREATE> <TABLE> {table} <(>\n" +
                    "【\r" +
                    "1:(<PRIMARY> <KEY> <(>{primaryColumn}<)> [<USING> {useValue}])|" +
                    "3:(<INDEX> {indexValue} <(>{column}<)>)|" +
                    "2:({column} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<COMMENT> {comment}])" +
                    ",\n】" +
                    "<)> [<COMMENT> <=> {comment}]"
    ),
    DROP_TABLE      ("<DROP> <TABLE> [<IF> <EXISTS>] {table}"),
    /**
     * 表重命名
     */
    RE_TABLE_NAME   ("ALTER TABLE {oldTable} RENAME TO {newTable}"),
    ALTER_ADD_MODIFY(
            "<ALTER> <TABLE> {table} <ADD|MODIFY> [COLUMN] {column} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<COMMENT> {comment}]"
    ),
    ALTER_ADD_MODIFY_MULTI(
            "<ALTER> <TABLE> {table} <ADD|MODIFY><(>\n" +
                    "【[COLUMN] {column} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<COMMENT> {comment}],\n】" +
                    "<)>"
    ),
    ALTER_CHANGE    (
            "<ALTER> <TABLE> {table} <CHANGE> {oldColumn} {newColumn} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}] [<COMMENT> {comment}]"
    ),
    COMMENT_TABLE   ("<ALTER> <TABLE> {table} <COMMENT> = '{comment}'"),
    COMMENT_COLUMN  ("<ALTER> <TABLE> {table} <MODIFY> `{column}` {dataType} [DEFAULT {defaultValue}] <COMMENT> '{comment}'"),

    /* =============================== DML操作语句 ==================================== */
    // （Data Manipulation Language）简称 DML：用来操纵数据库中数据的命令。包括：select、insert、update、delete。
    /*mysql可以用 SELECT SQL_CALC_FOUND_ROWS * FROM table
        LIMIT index;SELECT FOUND_ROWS();方法获得两个结果集*/
    SELECT_TABLE    ("SELECT * FROM {table}"),
    DB_TIME_SQL     ("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') as TIME"),
    COUNT_SIZE      ("SELECT COUNT(*) AS {totalAlias} FROM ({selectSql}) JNPF_TABLE"),
    COUNT_TABLE_SIZE(COUNT_SIZE.sqlFrame.replace("({selectSql})", "{table}")),
    INSERT          ("<INSERT> <INTO> {table} ([【{column},】]) <VALUES> (【{value},】)"),

    /* =============================== 后缀 ==================================== */
    // LIMIT必须在ORDER之后，在前报错。顺序先排序再分页。
    ORDER_PAGE      ("{selectSql} ORDER BY {orderColumn} [DESC] LIMIT {beginIndex},{pageSize}"),
    PAGE            ("{selectSql} LIMIT {beginIndex},{pageSize}"),
    ORDER           ("{selectSql} ORDER BY {column} [DESC]"),
    LIKE            ("{selectSql} WHERE {column} like {condition}"),


    ;

    /**
     *
     */
    private final String sqlFrame;
    private final String dbEncode = DbBase.MYSQL;

}
